﻿using log4net;
using Npgsql;
using NpgsqlTypes;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace PcaCore.DAL
{
    public class Core
    {
        /// <summary>
        /// Devuelve la cadena de conexion a la base de datos
        /// </summary>
        /// <returns>
        /// <see cref="System.String"/>
        /// </returns>
        public static string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["cnn"].ConnectionString;
        }

        public static string Base64Encode(string plainText)
        {
            var plainTextBytes = System.Text.Encoding.UTF8.GetBytes(plainText);
            return System.Convert.ToBase64String(plainTextBytes);
        }

        public static string Base64Decode(string base64EncodedData)
        {
            var base64EncodedBytes = System.Convert.FromBase64String(base64EncodedData);
            return System.Text.Encoding.UTF8.GetString(base64EncodedBytes);
        }

        /// <summary>
        /// deveulve la fecha actual del servidor de base de datos
        /// </summary>
        /// <returns></returns>
        public static DateTime GetCurrentDaTeTimeServer(string zonaHoraria)
        {
            using (NpgsqlConnection oCn = new NpgsqlConnection(GetConnectionString()))
            {
                string proc = string.Empty;
                proc = "SELECT current_timestamp AT TIME ZONE @zonaHoraria";
                NpgsqlCommand oCmd = new NpgsqlCommand(proc, oCn);

                oCmd.Parameters.Add("@zonaHoraria", NpgsqlDbType.Varchar).Value = zonaHoraria;

                oCn.Open();

                try
                {
                    return Convert.ToDateTime(oCmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        internal static int GetCountRowsTabla(string tablaId)
        {
            using (NpgsqlConnection oCn = new NpgsqlConnection(Core.GetConnectionString()))
            {
                string proc = string.Empty;
                proc = "pca_tablas.fn_get_count_rows_table";

                NpgsqlCommand oCmd = new NpgsqlCommand(proc, oCn);
                oCmd.CommandType = CommandType.StoredProcedure;

                oCmd.Parameters.Add(new NpgsqlParameter());
                oCmd.Parameters[0].NpgsqlDbType = NpgsqlDbType.Name;
                oCmd.Parameters[0].Value = tablaId;

                oCn.Open();

                try
                {
                    return Convert.ToInt32(oCmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        /// <summary>
        /// devuelve una secuencia de bytes
        /// </summary>
        /// <param name="ruta"></param>
        /// <returns></returns>
        public static byte[] FileToBytes(string ruta)
        {
            return File.ReadAllBytes(ruta);
        }

        public static Stream GetRemoteContent(string urlContent)
        {
            HttpWebRequest myWenRequest = (HttpWebRequest)WebRequest.Create(urlContent);

            HttpWebResponse myWebResponse = (HttpWebResponse)myWenRequest.GetResponse();

            return myWebResponse.GetResponseStream();
        }

        public static Image GetRemoteImage(string urlImage)
        {
            try
            {
                return Image.FromStream(GetRemoteContent(urlImage));
            }
            catch
            {
                throw new Exception("Imagen no Valida");
            }
        }

        internal static string[,] GetFiltrosArray(List<oFiltro> filtros)
        {
            string[,] arrayFiltros = new string[filtros.Count, 4];

            for (int i = 0; i < filtros.Count; i++)
            {
                arrayFiltros[i, 0] = filtros[i].type;
                arrayFiltros[i, 1] = filtros[i].comparison;
                arrayFiltros[i, 2] = filtros[i].value;
                arrayFiltros[i, 3] = filtros[i].field;
            }

            return arrayFiltros;
        }

        public static bool IsInteger(string input)
        {

            Regex regex = new Regex(@"^[0-9]+$");
            return regex.IsMatch(input);
        }

        public static bool IsDecimal(string input)
        {

            Regex regex = new Regex(@"^[0-9]{1,9}([\.\,][0-9]{1,3})?$");
            return regex.IsMatch(input);

        }

        public static DateTime GetDateTimeExtNet(string value)
        {
            string strFecha = value.Replace(char.Parse("\""), char.Parse(" ")).Trim();
            return DateTime.Parse(strFecha);
        }

        #region Metodos para consultar la base de datos

        private static Type currentClassType;
        private static PropertyInfo[] currentPropertysInfo;

        private static T FillDR<T>(IDataRecord myDR)
        {
            Type classType = typeof(T);
            PropertyInfo[] propertysInfo;


            object myClass = Activator.CreateInstance(classType);

            if (classType == currentClassType)
            {
                propertysInfo = currentPropertysInfo; 
            }
            else
            {
                propertysInfo = classType.GetProperties(); 
                currentClassType = classType;
                currentPropertysInfo = propertysInfo;
            }

            foreach (PropertyInfo item in propertysInfo)
            {
                if (myDR[item.Name] != DBNull.Value) classType.GetProperty(item.Name).SetValue(myClass, myDR[item.Name]);
            }

            return (T)Convert.ChangeType(myClass, typeof(T));
        }

        public static void ExecuteNonQuery(string functionName, params oParametro[] parameters)
        {
            using (NpgsqlConnection oCn = new NpgsqlConnection(Core.GetConnectionString()))
            {
                string proc = functionName;
                NpgsqlCommand oCmd = new NpgsqlCommand(proc, oCn);
                oCmd.CommandType = CommandType.StoredProcedure;
                int i = 0;

                foreach (oParametro item in parameters)
                {
                    oCmd.Parameters.Add(new NpgsqlParameter());
                    oCmd.Parameters[i].NpgsqlDbType = item.GetDbType;
                    oCmd.Parameters[i].Value = item.GetValue;
                    //if (item.GetDirection != ParameterDirection.Output)
                    //{
                    //    oCmd.Parameters[i].Value = item.GetValue;
                    //}
                    //oCmd.Parameters[i].Direction = item.GetDirection;
                    i++;
                }

                oCn.Open();

                oCmd.ExecuteNonQuery();
            }
        }

        public static object ExecuteScalar(string functionName, params oParametro[] parameters)
        {
            using (NpgsqlConnection oCn = new NpgsqlConnection(Core.GetConnectionString()))
            {
                string proc = functionName;
                NpgsqlCommand oCmd = new NpgsqlCommand(proc, oCn);
                oCmd.CommandType = CommandType.StoredProcedure;
                int i = 0;

                foreach (oParametro item in parameters)
                {
                    oCmd.Parameters.Add(new NpgsqlParameter());
                    oCmd.Parameters[i].NpgsqlDbType = item.GetDbType;
                    oCmd.Parameters[i].Value = item.GetValue;
                    i++;
                }

                oCn.Open();

                return oCmd.ExecuteScalar();
            }
        }

        public static T GetObjectDb<T>(string functionName, params oParametro[] parameters)
        {
            Type classType = typeof(T);
            T myClass = default(T);

            using (NpgsqlConnection oCn = new NpgsqlConnection(Core.GetConnectionString()))
            {
                string proc = functionName;
                NpgsqlCommand oCmd = new NpgsqlCommand(proc, oCn);
                oCmd.CommandType = CommandType.StoredProcedure;
                int i = 0;

                foreach (oParametro item in parameters)
                {
                    oCmd.Parameters.Add(new NpgsqlParameter());
                    oCmd.Parameters[i].NpgsqlDbType = item.GetDbType;
                    oCmd.Parameters[i].Value = item.GetValue;
                    i++;
                }

                oCn.Open();

                using (NpgsqlDataReader oRdr = oCmd.ExecuteReader())
                {
                    while (oRdr.Read())
                    {
                        myClass = FillDR<T>(oRdr);
                    }
                }

                return myClass;
            }
        }

        public static List<T> GetListObjecstDb<T>(string functionName, params oParametro[] parameters)
        {
            List<T> myList = new List<T>();

            using (NpgsqlConnection oCn = new NpgsqlConnection(Core.GetConnectionString()))
            {
                string proc = functionName;
                NpgsqlCommand oCmd = new NpgsqlCommand(proc, oCn);
                oCmd.CommandType = CommandType.StoredProcedure;
                int i = 0;

                foreach (oParametro item in parameters)
                {
                    oCmd.Parameters.Add(new NpgsqlParameter());
                    oCmd.Parameters[i].NpgsqlDbType = item.GetDbType;
                    oCmd.Parameters[i].Value = item.GetValue;
                    i++;
                }

                oCn.Open();

                using (NpgsqlDataReader oRdr = oCmd.ExecuteReader())
                {
                    while (oRdr.Read())
                    {
                        myList.Add(FillDR<T>(oRdr));
                    }
                }

                return myList;
            }
        }

        public static List<T> GetListObjecstDb<T>(string functionName, out int totalRecords, params oParametro[] parameters)
        {
            List<T> myList = new List<T>();
            totalRecords = 0;

            using (NpgsqlConnection oCn = new NpgsqlConnection(Core.GetConnectionString()))
            {
                string proc = functionName;
                NpgsqlCommand oCmd = new NpgsqlCommand(proc, oCn);
                oCmd.CommandType = CommandType.StoredProcedure;
                int i = 0;

                foreach (oParametro item in parameters)
                {
                    oCmd.Parameters.Add(new NpgsqlParameter());
                    oCmd.Parameters[i].NpgsqlDbType = item.GetDbType;
                    oCmd.Parameters[i].Value = item.GetValue;
                    i++;
                }

                oCn.Open();

                NpgsqlTransaction t = oCn.BeginTransaction();

                using (NpgsqlDataReader oRdr = oCmd.ExecuteReader())
                {
                    while (oRdr.Read())
                    {
                        myList.Add(FillDR<T>(oRdr));
                    }

                    oRdr.NextResult();

                    while (oRdr.Read())
                    {
                        totalRecords = Convert.ToInt32(oRdr[0]);
                    }
                }

                t.Commit();

                return myList;
            }
        }

        public static Exception LogException(ILog log, Exception ex, string nombreMetodo)
        {
            //No Guardar Logs para Codigos P0001(RAISE EXCEPTION) ya que son errores programados
            if (ex is NpgsqlException)
            {
                if ((ex as NpgsqlException).Code == "P0001")
                    return ex;
            }

            log.Error(String.Format("Metodo: {0} - {1}", nombreMetodo, ex.Message), ex);
            return ex;
        }

        #endregion
    }

    public class oCampo
    {
        public int id { get; set; }
        public string nombre { get; set; }

        public oCampo(int id, string nombre)
        {
            this.id = id;
            this.nombre = nombre;
        }
    }

    public class oFiltro
    {
        public string type { get; set; }
        public string comparison { get; set; }
        public string value { get; set; }
        public string field { get; set; }
    }

    public class oOrden
    {
        private string _direction;

        public string field { get; set; }
        public string direction
        {
            get
            {
                return _direction;
            }
            set
            {
                if (value != "DESC" || value != "ASC")
                    throw new Exception(String.Format("{0} no es un valor valido para direction", value));
                _direction = value;
            }
        }
    }

    public class oParametro
    {
        private NpgsqlDbType _dbType;
        private object _value;
        private ParameterDirection _direction;

        public oParametro(NpgsqlDbType dbType, object value, ParameterDirection direction = ParameterDirection.Input)
        {
            _dbType = dbType;
            _value = value;
            _direction = direction;
        }

        public NpgsqlDbType GetDbType { get { return _dbType; } }
        public object GetValue { get { return _value; } }

        public ParameterDirection GetDirection { get { return _direction; } }
    }
}
